{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "oriental-federation",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There was no error!\n",
      "输出成功，请打开 test.xlsx 查看输出结果\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# 1. 建交互控制台\n",
    "from yolib.convertter import pattern_copier\n",
    "copier = pattern_copier()\n",
    "# 2. 请求读取数据\n",
    "copier.read_data(data_file=r'.\\data\\欧冶链金台账.xlsx', idx=0, factory_name='欧冶链金', receipt='入仓单')\n",
    "\n",
    "# 11. 请求读取模板\n",
    "copier.read_pattern(r'.\\data\\出入库单模板.xlsx',0)\n",
    "\n",
    "# copier.tidy_data = copier.tidy_data[(copier.tidy_data.日期=='2021-7-23')]\n",
    "\n",
    "# copier.tidy_data['供货方'] = '郭薇'\n",
    "copier.tidy_data['保管员'] = '梅林雪'\n",
    "copier.oto_loc.update({'F13': '保管员'})\n",
    "# copier.tidy_data['车号'] = 'A1458'\n",
    "\n",
    "# copier.tidy_data.货物名称 = copier.tidy_data.货物名称.str.replace('外购','')\n",
    "\n",
    "# 15. 请求写入sheet\n",
    "copier.write_in_sheet(gap_between_blocks_in_a_page=0,number_of_blocks_per_page=1)\n",
    "\n",
    "# 19. sheet 页面设置\n",
    "copier.page_setup(margins=copier.NARROWER,paperSize='0',scale=100,)\n",
    "\n",
    "# 21. 输出结果\n",
    "copier.output('test.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "renewable-diana",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>收货方</th>\n",
       "      <th>合同编号</th>\n",
       "      <th>货物名称</th>\n",
       "      <th>数量</th>\n",
       "      <th>供货方</th>\n",
       "      <th>开票金额</th>\n",
       "      <th>单位</th>\n",
       "      <th>结算点</th>\n",
       "      <th>实付金额</th>\n",
       "      <th>应收款（税点）</th>\n",
       "      <th>实收金额</th>\n",
       "      <th>备注</th>\n",
       "      <th>通知开票人</th>\n",
       "      <th>Unnamed: 12</th>\n",
       "      <th>Unnamed: 13</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "      <th>Unnamed: 15</th>\n",
       "      <th>车号</th>\n",
       "      <th>保管员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44441.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021080050</td>\n",
       "      <td>三级工业打包块</td>\n",
       "      <td>684.30</td>\n",
       "      <td>潘威</td>\n",
       "      <td>2393568.04</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>2.209263e+06</td>\n",
       "      <td>0.077</td>\n",
       "      <td>184304.7400</td>\n",
       "      <td>3/9已付</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44442.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021080042</td>\n",
       "      <td>三级工业打包块</td>\n",
       "      <td>22.74</td>\n",
       "      <td>朱珠</td>\n",
       "      <td>77100.74</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>7.116398e+04</td>\n",
       "      <td>0.077</td>\n",
       "      <td>5936.7600</td>\n",
       "      <td>3/9已付</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>44442.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021080042</td>\n",
       "      <td>三级工业打包块</td>\n",
       "      <td>41.63</td>\n",
       "      <td>杨玉芬</td>\n",
       "      <td>139632.95</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>1.288812e+05</td>\n",
       "      <td>0.077</td>\n",
       "      <td>10751.7400</td>\n",
       "      <td>3/9已付</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>44442.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021080042</td>\n",
       "      <td>三级工业打包块</td>\n",
       "      <td>55.46</td>\n",
       "      <td>夏秀平</td>\n",
       "      <td>122231.37</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>1.128196e+05</td>\n",
       "      <td>0.077</td>\n",
       "      <td>9411.8100</td>\n",
       "      <td>3/9已付</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>44442.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021080046</td>\n",
       "      <td>二级重废</td>\n",
       "      <td>336.92</td>\n",
       "      <td>郭雪梅</td>\n",
       "      <td>1322832.17</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>1.220974e+06</td>\n",
       "      <td>0.077</td>\n",
       "      <td>101858.0800</td>\n",
       "      <td>3/9已付</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>44490.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021100007</td>\n",
       "      <td>一级钢筋打包块</td>\n",
       "      <td>47.74</td>\n",
       "      <td>吴竹英</td>\n",
       "      <td>193801.72</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>1.788790e+05</td>\n",
       "      <td>0.077</td>\n",
       "      <td>14922.7300</td>\n",
       "      <td>20/10付款</td>\n",
       "      <td>姜琴</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>44490.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021100007</td>\n",
       "      <td>一级钢筋打包块</td>\n",
       "      <td>50.52</td>\n",
       "      <td>朱珠</td>\n",
       "      <td>206058.31</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>1.901918e+05</td>\n",
       "      <td>0.077</td>\n",
       "      <td>15866.4900</td>\n",
       "      <td>20/10付款</td>\n",
       "      <td>姜琴</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>44495.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021090019</td>\n",
       "      <td>废钢</td>\n",
       "      <td>59.82</td>\n",
       "      <td>雷生焕</td>\n",
       "      <td>243128.22</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.923</td>\n",
       "      <td>2.244074e+05</td>\n",
       "      <td>0.077</td>\n",
       "      <td>18720.8700</td>\n",
       "      <td>17/10付款</td>\n",
       "      <td>王龙龙</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>44503.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021110005</td>\n",
       "      <td>一级钢筋打包块</td>\n",
       "      <td>381.90</td>\n",
       "      <td>夏秀平</td>\n",
       "      <td>1481298.44</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.924</td>\n",
       "      <td>1.368720e+06</td>\n",
       "      <td>0.076</td>\n",
       "      <td>112578.6800</td>\n",
       "      <td>3/11付款</td>\n",
       "      <td>乐亚文</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>44504.0</td>\n",
       "      <td>欧冶链金（湖北）再生资源有限公司</td>\n",
       "      <td>HBCG2021110006</td>\n",
       "      <td>一级钢筋打包块</td>\n",
       "      <td>395.20</td>\n",
       "      <td>何祥熙</td>\n",
       "      <td>1532885.95</td>\n",
       "      <td>吨</td>\n",
       "      <td>0.924</td>\n",
       "      <td>1.416387e+06</td>\n",
       "      <td>0.076</td>\n",
       "      <td>116499.3322</td>\n",
       "      <td>5/11付款</td>\n",
       "      <td>乐亚文</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A1458</td>\n",
       "      <td>梅林雪</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>113 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期               收货方            合同编号     货物名称      数量  供货方  \\\n",
       "0    44441.0  欧冶链金（湖北）再生资源有限公司  HBCG2021080050  三级工业打包块  684.30   潘威   \n",
       "1    44442.0  欧冶链金（湖北）再生资源有限公司  HBCG2021080042  三级工业打包块   22.74   朱珠   \n",
       "2    44442.0  欧冶链金（湖北）再生资源有限公司  HBCG2021080042  三级工业打包块   41.63  杨玉芬   \n",
       "3    44442.0  欧冶链金（湖北）再生资源有限公司  HBCG2021080042  三级工业打包块   55.46  夏秀平   \n",
       "4    44442.0  欧冶链金（湖北）再生资源有限公司  HBCG2021080046     二级重废  336.92  郭雪梅   \n",
       "..       ...               ...             ...      ...     ...  ...   \n",
       "109  44490.0  欧冶链金（湖北）再生资源有限公司  HBCG2021100007  一级钢筋打包块   47.74  吴竹英   \n",
       "110  44490.0  欧冶链金（湖北）再生资源有限公司  HBCG2021100007  一级钢筋打包块   50.52   朱珠   \n",
       "111  44495.0  欧冶链金（湖北）再生资源有限公司  HBCG2021090019       废钢   59.82  雷生焕   \n",
       "113  44503.0  欧冶链金（湖北）再生资源有限公司  HBCG2021110005  一级钢筋打包块  381.90  夏秀平   \n",
       "114  44504.0  欧冶链金（湖北）再生资源有限公司  HBCG2021110006  一级钢筋打包块  395.20  何祥熙   \n",
       "\n",
       "           开票金额 单位    结算点          实付金额  应收款（税点）         实收金额       备注 通知开票人  \\\n",
       "0    2393568.04  吨  0.923  2.209263e+06    0.077  184304.7400    3/9已付   NaN   \n",
       "1      77100.74  吨  0.923  7.116398e+04    0.077    5936.7600    3/9已付   NaN   \n",
       "2     139632.95  吨  0.923  1.288812e+05    0.077   10751.7400    3/9已付   NaN   \n",
       "3     122231.37  吨  0.923  1.128196e+05    0.077    9411.8100    3/9已付   NaN   \n",
       "4    1322832.17  吨  0.923  1.220974e+06    0.077  101858.0800    3/9已付   NaN   \n",
       "..          ... ..    ...           ...      ...          ...      ...   ...   \n",
       "109   193801.72  吨  0.923  1.788790e+05    0.077   14922.7300  20/10付款    姜琴   \n",
       "110   206058.31  吨  0.923  1.901918e+05    0.077   15866.4900  20/10付款    姜琴   \n",
       "111   243128.22  吨  0.923  2.244074e+05    0.077   18720.8700  17/10付款   王龙龙   \n",
       "113  1481298.44  吨  0.924  1.368720e+06    0.076  112578.6800   3/11付款   乐亚文   \n",
       "114  1532885.95  吨  0.924  1.416387e+06    0.076  116499.3322   5/11付款   乐亚文   \n",
       "\n",
       "    Unnamed: 12 Unnamed: 13  Unnamed: 14  Unnamed: 15     车号  保管员  \n",
       "0           NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "1           NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "2           NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "3           NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "4           NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "..          ...         ...          ...          ...    ...  ...  \n",
       "109         NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "110         NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "111         NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "113         NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "114         NaN         NaN          NaN          NaN  A1458  梅林雪  \n",
       "\n",
       "[113 rows x 20 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "copier.tidy_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "younger-motivation",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
